﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports System.Data
Imports System.Data.OleDb

Public Class ListasDA
    Implements DIL.ListasDI

    Dim stringConnection As String = "Microforma.My.MySettings.Setting" 'TODO: HardCode Cadena Conexion estática en 

    Public Function Add(ByVal listasBE As BEL.ListasBE, Optional ByVal isInternal As Boolean = Nothing) As String Implements DIL.ListasDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection) 'TODO: HardCode
        Dim query As String

        query = "Insert into Listas (Nombre,Contenido,ListaInterna,ListaPadre,SqlSelect,CodigoInterno,CampoMostrar,CampoRelacionPadre,Estado)" _
                                & "Values ('" & listasBE.Nombre & "','" & listasBE.Contenido & "'," & If(listasBE.ListaInterna, 1, 0) & "," & listasBE.ListaPadre _
                                & ",'" & listasBE.SqlSelect & "','" & listasBE.CodigoInterno & "','" & listasBE.CampoMostrar _
                                & "','" & listasBE.CampoRelacionPadre & "','" & listasBE.Estado _
                                & "')"

        If isInternal Then
            query = "Insert into Listas (Nombre,Contenido,ListaInterna,Estado)" _
                                    & "Values ('" & listasBE.Nombre & "','" & listasBE.Contenido & "'," & If(listasBE.ListaInterna, 1, 0) & ",'" & listasBE.Estado _
                                    & "')"
        End If


        Try


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

    Public Function Edit(ByVal listasBE As BEL.ListasBE) As String Implements DIL.ListasDI.Edit
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Try
            Dim query As String

            If listasBE.ListaInterna Then
                query = "Update Listas Set Nombre='" & listasBE.Nombre _
                                     & "',Contenido='" & listasBE.Contenido _
                                     & "',ListaInterna=" & CInt(listasBE.ListaInterna) * -1 _
                                     & ",Estado='" & listasBE.Estado _
                                     & "' where IdLista=" & listasBE.IdLista

            Else
                query = "Update Listas Set Nombre='" & listasBE.Nombre _
                                     & "',Contenido='" & listasBE.Contenido _
                                     & "',ListaInterna=" & CInt(listasBE.ListaInterna) * -1 _
                                     & ",ListaPadre=" & listasBE.ListaPadre _
                                     & ",SqlSelect='" & listasBE.SqlSelect _
                                     & "',CodigoInterno='" & listasBE.CodigoInterno _
                                     & "',CampoMostrar='" & listasBE.CampoMostrar _
                                     & "',CampoRelacionPadre='" & listasBE.CampoRelacionPadre _
                                     & "',Estado='" & listasBE.Estado _
                                     & "',IdDatoExterno=" & listasBE.IdDatoExterno _
                                     & " where IdLista=" & listasBE.IdLista

            End If



            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function List() As System.Data.DataTable Implements DIL.ListasDI.List

        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Dim query As String = "select * from Listas where Estado= '0' "

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

    Public Function list(ByVal idLista As Int16) As DataTable Implements DIL.ListasDI.List

        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Dim query As String = "select * from Listas where Estado= '0' and IdLista = " & idLista

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)
    End Function

    Public Function TestQuery(ByVal stringConnection As String, ByVal strQuerySql As String) As DataTable Implements ListasDI.TestQuery
        Try

            Dim OleCnx As New OleDbConnection(stringConnection)
            OleCnx.Open()

            Dim OleCom As New OleDbCommand
            OleCom.Connection = OleCnx
            OleCom.CommandType = CommandType.Text
            OleCom.CommandText = strQuerySql

            Dim table As New DataTable
            Dim OleDA As New OleDbDataAdapter(OleCom)
            OleDA.Fill(table)

            Return table
        Catch ex As System.Exception
            Return Nothing
        End Try
    End Function

    Public Function Delete(ByVal idLista As Int16) As String Implements DIL.ListasDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Try
            Dim query As String = "Delete from Listas where IdLista= '" & idLista & "'"

            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

            db.ExecuteNonQuery(o_dbCommand)

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

End Class
